from selenium.common import TimeoutException
import pymysql
from flask import Flask, flash, session
from flask import render_template
from pymysql.cursors import DictCursor

from selenium.webdriver.support import expected_conditions as EC
from flask import request, jsonify
import time
import json
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.wait import WebDriverWait

app = Flask(__name__)
app.config['SECRET_KEY'] = '#8asdftt7'

def get_db_connection():
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='flask',
        cursorclass=DictCursor  # 指定游标类型
    )
    return connection

# @app.before_request
# def login_check():
#     path = request.path
#     print(path)
#     if path == '/register':
#         return redirect(url_for('register'))
#     username = session.get('username')
#     if not username:
#         if path =='/redirect_register':
#             return redirect(url_for('register'))
#         return render_template('login.html')
#     return redirect(url_for('{path}'.format(path=path)))


# @app.before_request
# def login_check():
#     # 不需要登录即可访问的路径列表
#     public_paths = ['/login', '/register', '/redirect_register']
#
#     # 获取当前请求路径
#     path = request.path
#     print(f"Checking path: {path}")
#
#     # 如果是公开路径，直接放行
#     if path in public_paths:
#         return
#
#     # 检查用户是否已登录
#     username = session.get('username')
#     if not username:
#         # 未登录用户访问需要登录的页面，重定向到登录页
#         return render_template('login.html')
#
#     # 用户已登录，正常处理请求
#     return None


#首页
@app.route('/')
def index():
    return render_template('spider.html')


#获取json输出
@app.route('/get_users')
def get_users():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute('SELECT * FROM user')
        users = cursor.fetchall()
    connection.close()
    return jsonify(users)

#登录
@app.route('/login',methods=['POST'])
def login():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        print(request.form.get('username'))
        cursor.execute('SELECT * FROM user')
        users = cursor.fetchall()
        for i in users:
            if i['username'] == request.form.get('username') and i['password'] == request.form.get('password'):
                connection.close()
                session['username'] = i['username']
                return render_template('menu.html')
        connection.close()
        flash('用户名或密码错误')
        return render_template('1.html')

#注册
@app.route('/register',methods=['POST'])
def register():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        cursor.execute('select username from user')
        users = cursor.fetchall()
        username = request.form.get('username')
        password = request.form.get('password')
        for i in users:
            if i['username'] == username:
                flash("用户已经存在，请更换用户名")
                return render_template('register.html')
        while (len(username.replace(" ", "")) == 0 or len(password.replace(" ", "")) == 0):
            flash("请输入非空账号和密码")
            return render_template('2.html')
        cursor.execute('insert into user (username, password) values (%s, %s)',(username, password))
        connection.commit()
        flash("注册成功，请登录")
        return render_template('1.html')

#删除单个
@app.route('/deleteById',methods=['POST','GET'])
def delete():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        #接收前端发送的消息
        id = request.args.get('id')
        cursor.execute('select * from user where id = %s',(id))
        uid = cursor.fetchall()
        if(uid):
            cursor.execute('delete from user where id = %s',(uid[0]['id'],))
            connection.commit()
            return 'success'
        return 'fail'

        # username = request.form.get('username')
        # cursor.execute('select * from user where username = %s',(username))
        # user = cursor.fetchall()
        # id = user[0]['id']
        # print(id)
        # cursor.execute('select id from user')
        # users = cursor.fetchall()
        # print(users)
        # for i in users:
        #     if i['id'] == id:
        #         cursor.execute('delete from user where id = %s',(i['id'],))
        #         flash("删除成功")
        #         connection.commit()
        #         return 'success'
        # flash("删除的用户不存在")
        # return render_template('menu.html')

#添加
@app.route('/add',methods=['POST'])
def add():
    connection = get_db_connection()
    with connection.cursor() as cursor:

        data = request.get_json()
        username = data['username']
        password = data['password']
        cursor.execute('select * from user where username = %s',(username))
        user = cursor.fetchall()
        if not user :
            if len(password) >0:
                cursor.execute('insert into user (username, password) values (%s, %s)',(username, password))
                connection.commit()
                return 'success'
        return 'fail'

        # username = request.form.get('username')
        # password = request.form.get('password')
        # cursor.execute('select username from user where username = %s',(username))
        # users = cursor.fetchall()
        # if not users:
        #     while(len(username.replace(" ",""))==0 or len(password.replace(" ",""))==0):
        #         flash("请输入非空账号和密码")
        #         return render_template('menu.html')
        #     cursor.execute('insert into user (username, password) values (%s, %s)',(username, password))
        #     connection.commit()
        #     flash("添加用户成功")
        #     return render_template('menu.html')
        # else:
        #     flash("用户名已存在")
        #     return render_template('menu.html')

#查找单个
@app.route('/find',methods=['POST','GET'])
def find():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        data = request.get_json()
        print(type(data))
        currentPage = request.args.get('currentPage')
        pageSize = request.args.get('pageSize')
        begin = (int(currentPage)-1) * int(pageSize)
        username = data.get('username')
        username = f"%{username}%"
        cursor.execute("select * from user where username like %s limit %s , %s",(username,begin,int(pageSize)))
        rs = cursor.fetchall()
        cursor.execute('select count(*) from user where username like %s',(username))
        count = cursor.fetchall()
        print(rs)
        print(type(rs))
        return jsonify({
            'rows':rs,
            'totalCount':count[0]['count(*)']
        })

        # username = request.form.get('username')
        # cursor.execute('select * from user where username like %s',(username))
        # users = cursor.fetchall()
        # return jsonify(users)

#查找全部
@app.route('/findall',methods=['POST','GET'])
def findall():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        currentPage = request.args.get('currentPage')
        print(currentPage)
        pageSize = request.args.get('pageSize')
        print(pageSize)
        begin = (int(currentPage)-1)*int(pageSize)
        cursor.execute('select * from user limit %s, %s',(begin, int(pageSize)))
        rows = cursor.fetchall()

        cursor.execute('select count(*) from user')
        totalCount = cursor.fetchall()
        # print(totalCount)
        # print(type(totalCount))
        # print(totalCount[0]['count(*)'])

        return jsonify({
            'totalCount': totalCount[0]['count(*)'],
            'rows': rows
        })
        # cursor.execute('select * from user')
        # result = cursor.fetchall()
        # return jsonify(result)

#判断用户是否存在
@app.route('/findName',methods=['GET'])
def findName():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        id = request.args.get('id')
        cursor.execute('select * from user where id = %s',(id))
        result = cursor.fetchall()
        # print(type(result))
        # print(result)
        uid = str(result[0]['id'])
        username = result[0]['username']
        password = result[0]['password']
        dic = {'uid':uid,'username':username,'password':password}
        if result:
            return jsonify(dic)
        return 'fail'
        # username = request.form.get('username')
        # cursor.execute('select * from user where username = %s',username)
        # users = cursor.fetchall()
        # if users:
        #     flash("用户存在，请修改")
        #     return render_template('update.html',users=users)
        # else:
        #     flash("用户名错误")
        #     return render_template('isExists.html')

#修改
@app.route('/update',methods=['POST'])
def update():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        id = request.args.get('id')
        data = request.get_json()
        print(data)
        username = data['username']
        password = data['password']
        cursor.execute('select * from user where id = %s',(id))
        result = cursor.fetchall()
        if result:
            if len(username) > 0 and len(password) > 0:
                cursor.execute('update user set username = %s, password = %s where id = %s',(username, password, id))
                connection.commit()
                return 'success'
        return 'fail'


        # username = request.form.get('username')
        # password = request.form.get('password')
        # id = request.form.get('userid')
        # while (len(username.replace(" ", "")) == 0 or len(password.replace(" ", "")) == 0):
        #     flash("请输入非空账号和密码")
        #     return render_template('update.html')
        # cursor.execute('update user set username = %s, password = %s where id = %s',(username, password, id))
        # connection.commit()
        # flash("修改成功")
        # return render_template('menu.html')

#批量删除
@app.route('/deletemany',methods=['POST'])
def deletemany():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        data = request.get_json()
        print(data)
        print(type(data))
        if data:
            for i in data:
                cursor.execute('delete from user where id = %s',(i))
            connection.commit()
            return 'success'
        return 'fail'
        # usernames = request.form.get('username')
        # names = usernames.split(',')
        # cursor.execute('select username from user')
        # users = cursor.fetchall()
        # count = 0
        # userlist = []
        # for user in users:
        #     userlist.append(user['username'])
        # print(userlist)
        # for name in names:
        #     if name in userlist:
        #         cursor.execute("delete from user where username = %s",(name))
        #         count +=1
        # connection.commit()
        # flash("本次一共删除了%d个用户" % count)
        # return render_template('menu.html')

#批量添加
@app.route('/addmany',methods=['POST'])
def addmany():
    connection = get_db_connection()
    with connection.cursor() as cursor:
        usernames = request.form.get('username')
        usernames.replace(" ", "")
        passwords = request.form.get('password')
        passwords.replace(" ", "")
        names = usernames.split(',')
        passwds = passwords.split(',')

        if(len(names)!=len(passwds)):
            flash("用户人数和密码数不匹配")
            return render_template('addmany.html')
        for i in range(len(names)):
            if(len(names[i])==0 or len(passwds[i])==0):
                flash("用户名或密码不能为空")
                return render_template('addmany.html')
            name =  names[i]
            password = passwds[i]
            cursor.execute('insert into user (username, password) values (%s, %s)',(name,password))
        connection.commit()
        flash("添加了%d个用户"%len(names))
        return render_template('menu.html')




#定义重定向路由
@app.route('/redirect_login')
def redirect_login():
    return render_template('1.html')

@app.route('/redirect_register')
def redirect_register():
    return render_template('2.html')

@app.route('/redirect_isExists')
def redirect_isExists():
    return render_template('isExists.html')

@app.route('/redirect_delete')
def redirect_delete():
    return render_template('delete.html')

@app.route('/redirect_find')
def redirect_find():
    return render_template('find.html')

@app.route('/redirect_findall')
def redirect_findall():
    return render_template('findall.html')

@app.route('/redirect_add')
def redirect_add():
    return render_template('add.html')

@app.route('/redirect_deletemany')
def redirect_deletemany():
    return render_template('deletemany.html')

@app.route('/redirect_addmany')
def redirect_addmany():
    return render_template('addmany.html')


@app.route('/stock')
def index2():
    return render_template('stock.html')

@app.route('/next')
def next():
    return render_template('stock_analysis_fixed.html')

# 获取所有股票数据
@app.route('/stocks/find', methods=['POST'])
def find_stocks():
    connection = get_db_connection()
    try:
        data = request.get_json() or {}
        currentPage = int(request.args.get('currentPage', 1))
        pageSize = int(request.args.get('pageSize', 10))
        begin = (currentPage - 1) * pageSize

        # 构建查询条件
        name = data.get('name', '')
        conditions = []
        params = []

        if name:
            conditions.append("name LIKE %s")
            params.append(f"%{name}%")

        where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""

        # 查询股票数据
        query = f"SELECT * FROM stock_data {where_clause} LIMIT %s, %s"
        params.extend([begin, pageSize])

        with connection.cursor() as cursor:
            cursor.execute(query, params)
            rows = cursor.fetchall()

            # 查询总记录数
            count_query = f"SELECT COUNT(*) as count FROM stock_data {where_clause}"
            cursor.execute(count_query, params[:-2])  # 移除分页参数
            total_count = cursor.fetchone()['count']

        return jsonify({
            'rows': rows,
            'totalCount': total_count
        })
    finally:
        connection.close()


# 添加股票
@app.route('/stocks/add', methods=['POST'])
def add_stock():
    connection = get_db_connection()
    try:
        data = request.get_json()

        # 检查股票是否已存在
        with connection.cursor() as cursor:
            cursor.execute('SELECT * FROM stock_data WHERE name = %s', (data['name'],))
            stock = cursor.fetchone()

            if stock:
                return jsonify({'success': False, 'message': '股票已存在'})

            # 处理市值单位
            market_cap = data['market_cap']
            if isinstance(market_cap, str):
                if '亿' in market_cap:
                    market_cap = float(market_cap.replace('亿', '')) * 1e8
                elif '万' in market_cap:
                    market_cap = float(market_cap.replace('万', '')) * 1e4
                else:
                    market_cap = float(market_cap)

            # 插入股票数据
            cursor.execute(
                '''INSERT INTO stock_data 
                   (name, latest_price, price_change, change_percentage, 
                    opening_price, highest_price, lowest_price, previous_close, market_cap, pe_ratio)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
                (data['name'], data['latest_price'], data['price_change'],
                 data['change_percentage'], data['opening_price'],
                 data['highest_price'], data['lowest_price'],
                 data['previous_close'], market_cap, data['pe_ratio'])
            )
            connection.commit()

        return jsonify({'success': True, 'message': '添加成功'})
    except Exception as e:
        connection.rollback()
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        connection.close()



# 修改股票
@app.route('/stocks/update', methods=['POST'])
def update_stock():
    connection = get_db_connection()
    try:
        stock_id = request.args.get('id')
        data = request.get_json()

        with connection.cursor() as cursor:
            # 检查股票是否存在
            cursor.execute('SELECT * FROM stock_data WHERE id = %s', (stock_id,))
            stock = cursor.fetchone()

            if not stock:
                return jsonify({'success': False, 'message': '股票不存在'})

            # 处理市值单位
            market_cap = data.get('market_cap', stock['market_cap'])
            if isinstance(market_cap, str):
                if '亿' in market_cap:
                    market_cap = float(market_cap.replace('亿', '')) * 1e8
                elif '万' in market_cap:
                    market_cap = float(market_cap.replace('万', '')) * 1e4
                else:
                    market_cap = float(market_cap)

            # 更新股票数据
            update_query = '''
                UPDATE stock_data 
                SET name = %s, latest_price = %s, price_change = %s, 
                    change_percentage = %s, opening_price = %s, 
                    highest_price = %s, lowest_price = %s, 
                    previous_close = %s, market_cap = %s, pe_ratio = %s
                WHERE id = %s
            '''
            cursor.execute(
                update_query,
                (data.get('name', stock['name']),
                 data.get('latest_price', stock['latest_price']),
                 data.get('price_change', stock['price_change']),
                 data.get('change_percentage', stock['change_percentage']),
                 data.get('opening_price', stock['opening_price']),
                 data.get('highest_price', stock['highest_price']),
                 data.get('lowest_price', stock['lowest_price']),
                 data.get('previous_close', stock['previous_close']),
                 market_cap,
                 data.get('pe_ratio', stock['pe_ratio']),
                 stock_id)
            )
            connection.commit()

        return jsonify({'success': True, 'message': '修改成功'})
    except Exception as e:
        connection.rollback()
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        connection.close()


# 删除单个股票
@app.route('/stocks/deleteById', methods=['GET'])
def delete_stock():
    connection = get_db_connection()
    try:
        stock_id = request.args.get('id')

        with connection.cursor() as cursor:
            # 检查股票是否存在
            cursor.execute('SELECT * FROM stock_data WHERE id = %s', (stock_id,))
            stock = cursor.fetchone()

            if not stock:
                return 'fail'

            # 删除股票
            cursor.execute('DELETE FROM stock_data WHERE id = %s', (stock_id,))
            connection.commit()

        return 'success'
    except Exception as e:
        connection.rollback()
        return 'fail'
    finally:
        connection.close()


# 批量删除股票
@app.route('/stocks/deleteMany', methods=['POST'])
def delete_many_stocks():
    connection = get_db_connection()
    try:
        data = request.get_json()

        if not data:
            return 'fail'

        with connection.cursor() as cursor:
            # 构建批量删除语句
            placeholders = ', '.join(['%s'] * len(data))
            query = f'DELETE FROM stock_data WHERE id IN ({placeholders})'
            cursor.execute(query, data)
            connection.commit()

        return 'success'
    except Exception as e:
        connection.rollback()
        return 'fail'
    finally:
        connection.close()


def find_next_page_button(driver):
    """智能查找下一页按钮，支持多种常见模式"""
    strategies = [
        {
            'name': '东方财富网模式',
            'xpath': '//*[@id="mainc"]/div/div/div[4]/div/a[last()]',
            'text_check': lambda text: text == '>'
        },
        {
            'name': '文本"下一页"按钮',
            'xpath': '//a[contains(text(), "下一页") or contains(text(), "Next")]',
            'text_check': lambda text: True
        },
        {
            'name': '分页容器中的下一页',
            'xpath': '//div[contains(@class, "pagination") or contains(@class, "page")]//a[last()]',
            'text_check': lambda text: '下一页' in text or 'next' in text.lower() or text == '>'
        },
        {
            'name': 'aria-label下一页按钮',
            'xpath': '//a[@aria-label="下一页"]',
            'text_check': lambda text: True
        }
    ]

    for strategy in strategies:
        try:
            buttons = driver.find_elements(By.XPATH, strategy['xpath'])
            for button in buttons:
                if button.is_displayed() and button.is_enabled():
                    button_text = button.text.strip()
                    if strategy['text_check'](button_text):
                        return button, strategy['name']
        except Exception as e:
            print(f"尝试策略 '{strategy['name']}' 失败: {e}")

    return None, None


def analyze_page_for_pagination(driver):
    """分析页面结构，识别分页元素"""
    print("正在分析页面结构以识别分页元素...")

    pagination_containers = driver.find_elements(By.XPATH,
                                                 '//div[contains(@class, "pagination") or contains(@class, "page")]')

    if not pagination_containers:
        pagination_containers = driver.find_elements(By.XPATH,
                                                     '//div[contains(@id, "page") or contains(@class, "pager")]')

    if not pagination_containers:
        print("未识别到分页容器")
        return None, None

    print(f"识别到 {len(pagination_containers)} 个可能的分页容器")

    for container in pagination_containers:
        container_html = container.get_attribute('innerHTML')

        if "下一页" in container_html or "Next" in container_html or ">" in container_html:
            try:
                next_buttons = container.find_elements(By.XPATH,
                                                       './/a[contains(text(), "下一页") or contains(text(), "Next") or text()=">"]')
                if next_buttons:
                    return next_buttons[0], "基于文本识别"

                clickable_elements = container.find_elements(By.XPATH, './/a')
                if len(clickable_elements) > 1:
                    return clickable_elements[-1], "基于位置识别"
            except Exception as e:
                print(f"分析分页容器时出错: {e}")

    return None, None


def wait_for_element(driver, xpath, timeout=10):
    try:
        return WebDriverWait(driver, timeout).until(
            EC.presence_of_element_located((By.XPATH, xpath))
        )
    except TimeoutException:
        print(f"等待元素超时: {xpath}")
        return None


@app.route('/spider', methods=['POST'])
def spider():
    try:
        data = request.json
        url = data.get('url', "https://quote.eastmoney.com/center/gridlist.html#hs_a_board")
        filename = data.get('filename', 'spider_result.csv')
        file_type = data.get('fileType', 'csv')
        encoding = data.get('encoding', 'utf-8')

        all_data = []
        total_pages = 0  # 初始化变量，避免未定义错误

        # 配置驱动选项
        options = webdriver.EdgeOptions()
        options.add_argument('--start-maximized')
        options.add_argument('--disable-notifications')

        driver = webdriver.Edge(options=options)

        try:
            driver.get(url)

            # 等待页面加载
            time.sleep(3)

            def collect_data(html_content):
                soup = BeautifulSoup(html_content, 'lxml')
                trs = soup.find_all('tr')
                page_data = []
                for tr in trs[1:]:
                    tds = tr.find_all(['th', 'td'])
                    row = [td.get_text(strip=True) for td in tds]
                    if row:
                        page_data.append(row)
                return page_data

            num = 1
            while True:
                try:
                    print(f"正在处理第 {num} 页...")

                    # 等待表格加载
                    wait_for_element(driver, '//table')
                    html = driver.page_source

                    page_data = collect_data(html)
                    all_data.extend(page_data)
                    total_pages = num  # 更新总页数

                    print(f"第 {num} 页收集成功，共 {len(page_data)} 条记录")

                    # 查找下一页按钮
                    next_button, strategy_name = find_next_page_button(driver)

                    if not next_button:
                        next_button, strategy_name = analyze_page_for_pagination(driver)

                    if next_button:
                        if next_button.text != ">":
                            print("数据抓取完毕！")
                            break
                        print(f"使用策略 '{strategy_name}' 找到下一页按钮")

                        # 滚动到按钮位置
                        driver.execute_script("arguments[0].scrollIntoView();", next_button)
                        time.sleep(1)

                        try:
                            next_button.click()
                        except Exception as e:
                            print(f"直接点击失败，尝试JavaScript点击: {e}")
                            driver.execute_script("arguments[0].click();", next_button)

                        print(f"已点击下一页，等待页面加载...")
                        time.sleep(2)
                        num += 1
                    else:
                        print("未找到下一页按钮，爬取完成")
                        break

                except Exception as e:
                    print(f"处理单页时出错: {e}")
                    break

        finally:
            # 确保浏览器总是会被关闭
            driver.quit()

        # 处理数据格式
        if file_type == 'json':
            result = json.dumps(all_data, ensure_ascii=False, indent=2)
        elif file_type == 'csv':
            csv_data = []
            for row in all_data:
                csv_row = ','.join([f'"{cell}"' if ',' in str(cell) else str(cell) for cell in row])
                csv_data.append(csv_row)
            result = '\n'.join(csv_data)
        else:
            result = '\n'.join(['\t'.join(map(str, row)) for row in all_data])

        return jsonify({
            'success': True,
            'data': result,
            'totalPages': total_pages,  # 使用小写的 total_pages
            'message': f'成功爬取{total_pages}页数据'
        })

    except Exception as e:
        print(f"爬取过程出错: {e}")
        return jsonify({
            'success': False,
            'message': f'爬取失败: {str(e)}',
            'totalPages': 0  # 确保返回的 JSON 中包含 totalPages
        })

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=8080, debug=True)


if __name__ == '__main__':
    app.run(port=8080, debug=True)

